--tbl_status

CREATE PROC Status_GetAll
AS
	SELECT [status_id], [user_id], [text], [created_date] FROM [tbl_status]

GO

CREATE PROC Status_GetInfo
@status_id int 
AS
	SELECT [status_id], [user_id], [text], [created_date] FROM [tbl_status] WHERE [status_id] = @status_id
	
GO

CREATE PROC Status_Insert
@user_id int,
@text nvarchar(255),
@created_date datetime
AS
	INSERT INTO [tbl_status]([user_id], [text], [created_date]) VALUES(@user_id, @text, @created_date) 
GO

ALTER PROC Status_Insert
@user_id int,
@text nvarchar(255),
@created_date datetime,
@status_id int OUTPUT
AS
	SET NOCOUNT ON
	DECLARE @intErrorCode int
	SELECT @intErrorCode = @@ERROR
	IF @intErrorCode = 0
	BEGIN
		INSERT INTO [tbl_status]([user_id], [text], [created_date]) VALUES(@user_id, @text, @created_date) 
		SELECT @intErrorCode = @@ERROR, @status_id = @@IDENTITY
	END
	RETURN @intErrorCode
GO


CREATE PROC Status_Update
@status_id int,
@user_id int,
@text nvarchar(255),
@created_date datetime
AS
	UPDATE [tbl_status] 
	SET [user_id] = @user_id,
		[text] = @text,
		[created_date] = @created_date
	WHERE
		[status_id] = @status_id

GO

CREATE PROC Status_Delete
@status_id int
AS
	DELETE FROM [tbl_status] WHERE [status_id] = @status_id

GO

--tbl_comment_status
CREATE PROC CommentStatus_GetAll
AS
	SELECT [comment_id], [status_id], [friend_id], [text], [created_date] FROM [tbl_comment_status]

GO

CREATE PROC CommentStatus_GetInfo
@comment_id int
AS
	SELECT [comment_id], [status_id], [friend_id], [text], [created_date] FROM [tbl_comment_status] WHERE [comment_id] = @comment_id
	
GO

CREATE PROC CommentStatus_Insert
@status_id int,
@friend_id int,
@text nvarchar(255),
@created_date datetime
AS
	INSERT INTO [tbl_comment_status]([status_id], [friend_id], [text], [created_date]) 
		VALUES(@status_id, @friend_id, @text, @created_date)
GO

ALTER PROC CommentStatus_Insert
@status_id int,
@friend_id int,
@text nvarchar(255),
@created_date datetime,
@comment_id int OUTPUT
AS
	SET NOCOUNT ON
	DECLARE @intErrorCode int
	SELECT @intErrorCode = @@ERROR
	IF @intErrorCode = 0
	BEGIN
		INSERT INTO [tbl_comment_status]([status_id], [friend_id], [text], [created_date]) 
			VALUES(@status_id, @friend_id, @text, @created_date)
		SELECT @intErrorCode = @@ERROR, @comment_id = @@IDENTITY
	END
	RETURN @intErrorCode
GO

CREATE PROC CommentStatus_Update
@comment_id int,
@status_id int,
@friend_id int,
@text nvarchar(255),
@created_date datetime
AS
	UPDATE [tbl_comment_status]
	SET	[status_id] = @status_id,
		[friend_id] = @friend_id,
		[text] = @text,
		[created_date] = @created_date
	WHERE [comment_id] = @comment_id

GO

CREATE PROC CommentStatus_Delete
@comment_id int
AS
	DELETE FROM [tbl_comment_status] WHERE [comment_id] = @comment_id

GO

--tbl_notification

CREATE PROC Notification_GetAll
AS
	SELECT [notification_id], [user_id], [message], [type], [created_date] FROM [tbl_notification]

GO

CREATE PROC Notification_GetInfo
@notification_id int
AS
	SELECT [notification_id], [user_id], [message], [type], [created_date] FROM [tbl_notification] WHERE [notification_id] = @notification_id

GO

CREATE PROC Notification_Insert
@user_id int,
@message nvarchar(50),
@type tinyint,
@created_date datetime
AS
	INSERT INTO [tbl_notification]([user_id], [message], [type], [created_date])
		VALUES(@user_id, @message, @type, @created_date)
	
GO

ALTER PROC Notification_Insert
@user_id int,
@message nvarchar(50),
@type tinyint,
@created_date datetime,
@notification_id int OUTPUT
AS
	SET NOCOUNT ON
	DECLARE @intErrorCode int
	SELECT @intErrorCode = @@ERROR
	IF @intErrorCode = 0
	BEGIN
		INSERT INTO [tbl_notification]([user_id], [message], [type], [created_date])
			VALUES(@user_id, @message, @type, @created_date)
		SELECT @intErrorCode = @@ERROR, @notification_id = @@IDENTITY
	END
	RETURN @intErrorCode	
GO

CREATE PROC Notification_Update
@notification_id int,
@user_id int,
@message nvarchar(50),
@type tinyint,
@created_date datetime
AS
	UPDATE [tbl_notification]
	SET [user_id] = @user_id,
		[message] = @message,
		[type] = @type,
		[created_date] = @created_date
	WHERE [notification_id] = @notification_id
		
GO

CREATE PROC Notification_Delete
@notification_id int
AS
	DELETE FROM [tbl_notification] WHERE [notification_id] = @notification_id

GO

---tbl_member_list

CREATE PROC MemberList_GetAll
AS
	SELECT [member_list_id], [group_id], [description] FROM [tbl_member_list]
	
GO

CREATE PROC MemberList_GetInfo
@member_list_id int
AS
	SELECT [member_list_id], [group_id], [description] FROM [tbl_member_list] WHERE [member_list_id] = @member_list_id

GO

CREATE PROC MemberList_Insert
@group_id int,
@description nvarchar(255)
AS
	INSERT INTO [tbl_member_list]([group_id], [description])
		VALUES(@group_id, @description)
GO

ALTER PROC MemberList_Insert
@group_id int,
@description nvarchar(255),
@member_list_id int OUTPUT
AS
	SET NOCOUNT ON
	DECLARE @intErrorCode int
	SELECT @intErrorCode = @@ERROR
	IF @intErrorCode = 0
	BEGIN
		INSERT INTO [tbl_member_list]([group_id], [description])
			VALUES(@group_id, @description)
		SELECT @intErrorCode = @@ERROR, @member_list_id = @@IDENTITY
	END
	RETURN @intErrorCode
GO

CREATE PROC MemberList_Update
@member_list_id int,
@group_id int,
@description nvarchar(255)
AS
	UPDATE [tbl_member_list]
	SET [group_id] = @group_id,
		[description] = @description
	WHERE [member_list_id] = @member_list_id

GO

CREATE PROC MemberList_Delete
@member_list_id int
AS
	DELETE FROM [tbl_member_list] WHERE [member_list_id] = @member_list_id

GO

--tbl_photo

CREATE PROC Photo_GetAll
AS
	SELECT [photo_id], [album_id], [caption], [created_date], [is_shown], [name] FROM [tbl_photo]

GO

CREATE PROC Photo_GetInfo
@photo_id int
AS
	SELECT [photo_id], [album_id], [caption], [created_date], [is_shown], [name] FROM [tbl_photo] WHERE [photo_id] = @photo_id

GO

CREATE PROC Photo_Insert
@album_id int,
@caption nvarchar(50),
@created_date datetime,
@is_shown bit,
@name nvarchar(255)
AS
	INSERT INTO [tbl_photo]([album_id], [caption], [created_date], [is_shown], [name])
		VALUES(@album_id, @caption, @created_date, @is_shown, @name)

GO

ALTER PROC Photo_Insert
@album_id int,
@caption nvarchar(50),
@created_date datetime,
@is_shown bit,
@name nvarchar(255),
@photo_id int OUTPUT
AS
	SET NOCOUNT ON
	DECLARE @intErrorCode int
	SELECT @intErrorCode = @@ERROR
	IF @intErrorCode = 0
	BEGIN
		INSERT INTO [tbl_photo]([album_id], [caption], [created_date], [is_shown], [name])
			VALUES(@album_id, @caption, @created_date, @is_shown, @name)
		SELECT @intErrorCode = @@ERROR, @photo_id = @@IDENTITY
	END
	RETURN @intErrorCode
GO

CREATE PROC Photo_Update
@photo_id int,
@album_id int,
@caption nvarchar(50),
@created_date datetime,
@is_shown bit,
@name nvarchar(255)
AS
	UPDATE [tbl_photo]
	SET [album_id] = @album_id,
		[caption] = @caption,
		[created_date] =@created_date,
		[is_shown] = @is_shown,
		[name] = @name
	WHERE [photo_id] = @photo_id

GO

CREATE PROC Photo_Delete
@photo_id int
AS
	DELETE FROM [tbl_photo] WHERE [photo_id] = @photo_id
	
GO

--tbl_comment_photo


CREATE PROC CommentPhoto_GetAll
AS
	SELECT [comment_id], [photo_id], [friend_id], [text], [created_date] FROM [tbl_comment_photo]

GO

CREATE PROC CommentPhoto_GetInfo
@comment_id int
AS
	SELECT [comment_id], [photo_id], [friend_id], [text], [created_date] FROM [tbl_comment_photo] WHERE [comment_id] = @comment_id
	
GO

CREATE PROC CommentPhoto_Insert
@photo_id int,
@friend_id int,
@text nvarchar(255),
@created_date datetime
AS
	INSERT INTO [tbl_comment_photo]([photo_id], [friend_id], [text], [created_date]) 
		VALUES(@photo_id, @friend_id, @text, @created_date)
GO

ALTER PROC CommentPhoto_Insert
@photo_id int,
@friend_id int,
@text nvarchar(255),
@created_date datetime,
@comment_id int OUTPUT
AS
	SET NOCOUNT ON
	DECLARE @intErrorCode int
	SELECT @intErrorCode = @@ERROR
	IF @intErrorCode = 0
	BEGIN
		INSERT INTO [tbl_comment_photo]([photo_id], [friend_id], [text], [created_date]) 
			VALUES(@photo_id, @friend_id, @text, @created_date)
		SELECT @intErrorCode = @@ERROR, @comment_id = @@IDENTITY
	END
	RETURN @intErrorCode
GO

CREATE PROC CommentPhoto_Update
@comment_id int,
@photo_id int,
@friend_id int,
@text nvarchar(255),
@created_date datetime
AS
	UPDATE [tbl_comment_photo]
	SET	[photo_id] = @photo_id,
		[friend_id] = @friend_id,
		[text] = @text,
		[created_date] = @created_date
	WHERE [comment_id] = @comment_id

GO

CREATE PROC CommentPhoto_Delete
@comment_id int
AS
	DELETE FROM [tbl_comment_photo] WHERE [comment_id] = @comment_id

GO

ALTER PROC User_GetUserByMultipleCondition
@username varchar(32) = NULL,
@name_first nvarchar(50) = NULL,
@name_last nvarchar(50) = NULL,
@email varchar(100) = NULL,
@active tinyint = NULL,
@nick_name nvarchar(50) = NULL,
@gender bit = NULL,
@before_date datetime = NULL,
@after_date datetime = NULL
AS
	DECLARE @where_condition nvarchar(255)
	DECLARE @sql nvarchar(255)
	SET @sql = 'SELECT * FROM [tbl_user] INNER JOIN [tbl_profile] ON [tbl_user].[user_id] = [tbl_profile].[user_id]'
	SET @where_condition = ' WHERE'
	
	IF @username IS NOT NULL
	BEGIN
		SET @where_condition = @where_condition + ' [username] = ''' + @username + ''''
	END
	
	IF @name_first IS NOT NULL
	BEGIN
		SET @where_condition = @where_condition + ' AND [name_first] = N''' + @name_first + ''''	
	END
	
	IF @name_last IS NOT NULL
	BEGIN
		SET @where_condition = @where_condition + ' AND [name_last] = N''' + @name_last + ''''		
	END
	
	IF @email IS NOT NULL
	BEGIN
		SET @where_condition = @where_condition + ' AND [email] = ''' + @email + ''''		
	END
	
	IF @active IS NOT NULL
	BEGIN
		SET @where_condition = @where_condition + ' AND [active] = ' + @active		
	END
	
	IF @nick_name IS NOT NULL
	BEGIN
		SET @where_condition = @where_condition + ' AND [nick_name] = N''' + @nick_name + ''''		
	END

	IF @gender IS NOT NULL
	BEGIN
		IF @gender = 1
		BEGIN
			SET @where_condition = @where_condition + ' AND [gender] = 1'
		END
		ELSE
		BEGIN
			SET @where_condition = @where_condition + ' AND [gender] = 0'		
		END		
	END
	
	IF @before_date IS NOT NULL
	BEGIN
		SET @where_condition = @where_condition + ' AND [created_date] < ''' + @before_date + ''''
	END
	
	IF @after_date IS NOT NULL
	BEGIN
		SET @where_condition = @where_condition + ' AND [created_date] > ''' + @after_date + ''''
	END
	
	IF @where_condition <> ' WHERE'
	BEGIN
		SET @sql = @sql + @where_condition
	END
	
	--PRINT @sql
	EXEC(@sql)
GO

EXEC User_GetUserByMultipleCondition @username='trungdk',@nick_name=N'have', @email='trung.dk070384@gmail.com'